Pivots

At some point, somebody is going to want the data in their visualization to be pivoted. With bipp, this is accomplished with a single selection!

Select the columns:

Fetch the data:

Select a Pivot:

bipp generates the SQL (using MySQL as a backend):

WITH
raw_data AS (
    SELECT t000.stock_code AS _0, t000.country AS _1,
        sum(t000.invoice_qnty*t000.unit_price) AS _2
    FROM `online_retail`.`transactions` AS t000
    WHERE (t000.stock_code IS NOT NULL)
    GROUP BY 1, 2
),
pivot_values AS (
    SELECT _1, ROW_NUMBER() OVER (ORDER BY k DESC, _1) AS _colnum
    FROM (
        SELECT _1, COUNT(*) AS k
        FROM raw_data
        WHERE _1 IS NOT NULL
        GROUP BY _1
        ORDER BY k DESC, _1 LIMIT 100
    )
),
pivot_count AS (
    SELECT COUNT(*) AS C FROM (
       SELECT _1, COUNT(*) AS k FROM raw_data
       WHERE _1 IS NOT NULL
       GROUP BY _1
    )
)
SELECT NULL AS _0,
    CONCAT("`|", STRING_AGG(value, "~|" ORDER BY value)) as value,
    SUM(density) AS density
FROM (
    SELECT CONCAT("0:", CAST(C AS STRING)) AS value, 0 AS density
    FROM pivot_count
    UNION ALL
    SELECT STRING_AGG(CONCAT(CAST(_colnum AS STRING),":",CAST(_1 
        AS STRING)), "~|" ORDER BY _colnum) AS value, 101 AS density
    FROM pivot_values
    )
    UNION ALL
    SELECT _0, CONCAT("`|", STRING_AGG(CONCAT(CAST(_colnum AS
        STRING),":",CAST(_2 AS STRING)),"~|" ORDER BY _colnum)) AS
        value,
    COUNT(*) AS density FROM (
        SELECT raw_data._0, pivot_values._1, raw_data._2,
            pivot_values._colnum AS _colnum
        FROM raw_data
        JOIN pivot_values
        ON raw_data._1=pivot_values._1
        )
    GROUP BY 1
    ORDER BY 3 DESC

Fetch the data: